package com.fact.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import com.fact.dto.MarketDTO;
import com.fact.dto.NodeFactDTO;
import com.fact.dto.NodeHierarchyDTO;
import com.fact.utils.connection.ConnectionManager;

public class FactService {
	
	
	public ArrayList<NodeHierarchyDTO> getNodeHierarchy(int segmentId,int makeId,int marketId,int month,int year)
	{
		
		ArrayList<NodeHierarchyDTO> nodeHierarchyList=new ArrayList<NodeHierarchyDTO>();
		HashMap<String, ArrayList<NodeFactDTO>> nodeFactsHashMap=getNodeFacts(segmentId, makeId, marketId, month, year);
		Connection conn = ConnectionManager.getConnection();
		String sql="select * from node_hierarchy Where Segment_id=? Order by Position";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1, segmentId);
				r =  stmt.executeQuery();
				
				NodeHierarchyDTO nhDTO=null;
				while (r.next())
				{
					nhDTO=new NodeHierarchyDTO();
					
					nhDTO.setNodeId(r.getInt("NODE_ID"));
					nhDTO.setDataType(r.getString("DATA_TYPE"));
					nhDTO.setDescription(r.getString("DESCRIPTION"));
					nhDTO.setLabel(r.getString("LABEL"));
					nhDTO.setPosition(r.getInt("POSITION"));
					nhDTO.setParentNode(r.getInt("PARENT_NODE"));
					nhDTO.setNodeType(r.getInt("NODE_TYPE_ID"));
					
					System.out.println(r.getInt("NODE_ID"));
					
					System.out.println("---Before--");
					//nhDTO.setNodeFactList(getNodeFacts(segmentId, result.getInt("NODE_ID"), makeId, marketId, month, year));
					nhDTO.setNodeFactList(getNodeFactsFromHash(nodeFactsHashMap,segmentId, r.getInt("NODE_ID"), makeId, marketId, month, year));
					System.out.println("---After--");
					
					nodeHierarchyList.add(nhDTO);
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return nodeHierarchyList;
	}
	
	public HashMap<String, ArrayList<NodeFactDTO>> getNodeFacts(int segmentId, int makeId,int marketId,int month,int year)
	{
		ArrayList<NodeFactDTO> factList=new ArrayList<NodeFactDTO>();
		HashMap<String, ArrayList<NodeFactDTO>> hashMap=new HashMap<String, ArrayList<NodeFactDTO>>();

		PreparedStatement stmt = null;
		ResultSet r = null;
		
		Connection conn = ConnectionManager.getConnection();
		String sql="Select * from Node_Facts nf, MST_SCENARIO ms,NODE_SCENARIO_ATTRIBUTES msa Where" +
		" Entry_Set_Id=(Select Entry_Set_Id from ENTRY_SETS Where Month=? and Year=? and  " +
		" Make_Id=? and Market_Id=? and Segment_id=?) " +
		" and nf.SCENARIO_ID=ms.SCENARIO_ID " +
		" and ms.SCENARIO_ID=msa.SCENARIO_ID " +
		" and nf.NODE_ID=msa.NODE_ID " +
		" And msa.MAKE_ID=? "+
		" and msa.VISIBILITY_FLAG=1 order by nf.Node_Id,ms.SCENARIO_ID";
		   
		try
		{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, segmentId);

			stmt.setInt(1, month);
			stmt.setInt(2, year);
			stmt.setInt(3, makeId);
			stmt.setInt(4, marketId);
			stmt.setInt(5, segmentId);
			stmt.setInt(6, makeId);
			r =  stmt.executeQuery();
			String key="";
			NodeFactDTO nfDTO=null;
			int preNode=0;
			boolean flag=false;
			while (r.next())
			{   
				if(preNode!=r.getInt("Node_Id") && preNode!=0)
				{
					key=segmentId+""+preNode+""+makeId+""+marketId+""+month+""+year;
					hashMap.put(key, factList);
					factList=new ArrayList<NodeFactDTO>();
					flag=false;				
				}
				
				nfDTO=new NodeFactDTO();
				nfDTO.setEntrySetId(r.getInt("ENTRY_SET_ID"));
				nfDTO.setMakeId(r.getInt("MAKE_ID"));
				nfDTO.setNodeId(r.getInt("NODE_ID"));
				nfDTO.setScenarioId(r.getInt("SCENARIO_ID"));
				nfDTO.setScenarioName(r.getString("SCENARIO_NAME"));
				nfDTO.setPosition(r.getInt("POSITION_NO"));
				nfDTO.setVisibility(r.getBoolean("VISIBILITY_FLAG"));
				nfDTO.setMinValue(r.getInt("MIN_VALUE"));
				nfDTO.setMaxValue(r.getInt("MAX_VALUE"));	
				nfDTO.setValue(r.getInt("VALUE"));	
				factList.add(nfDTO);
				flag=true;

				preNode=r.getInt("Node_Id");
			}
		
		
				if(flag==true)
				{
					key=segmentId+""+preNode+""+makeId+""+marketId+""+month+""+year;
					hashMap.put(key, factList);
				}
		}
		
		catch (Exception e){e.printStackTrace();}
 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
			{e.printStackTrace();}stmt = null;conn = null;}
		
		return hashMap;
	}
	
	public ArrayList<NodeFactDTO> getNodeFactsFromHash(HashMap<String, ArrayList<NodeFactDTO>> nodeFactsHashMap,int segmentId,int nodeId, int makeId,int marketId,int month,int year) throws SQLException
	{
		ArrayList<NodeFactDTO> factList=new ArrayList<NodeFactDTO>();
		String key=segmentId+""+nodeId+""+makeId+""+marketId+""+month+""+year;
		factList=nodeFactsHashMap.get(key);
		return factList;
	}
	
	public boolean saveKPIvalue(NodeFactDTO kpi)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Update NODE_FACTS  set VALUE=?  Where ENTRY_SET_ID=? AND NODE_ID=? AND SCENARIO_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setFloat(1,kpi.getValue());
				stmt.setInt(2,kpi.getEntrySetId());
				stmt.setInt(3,kpi.getNodeId());
				stmt.setInt(4,kpi.getScenarioId());
				
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}

}
